﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

public partial class CrystalReport_SubsidyReleaseSituation : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            BindDate();
        if (Session["UserStreet"] != null)
        {
            DataSetCrystal dsc = new DataSetCrystal();
            string connString = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
            string[] arrayadd = new string[8] { "江厦", "月湖", "南门", "鼓楼", "西门", "白云", "望春", "段塘" };
            int time = Convert.ToInt32(ddlyear.SelectedValue) * 100 + Convert.ToInt32(ddlmonth.SelectedValue);
            for (int i = 0; i < 8; i++)
            {
                float Snew = 0, Cnew = 0, Sadd = 0, Cadd = 0, Sred = 0, Cred = 0, Sbf = 0, Cbf = 0, Stotal=0, Ctotal=0 ;
                DataTable dt = dsc.Tables["SubsidyReleaseSituation"];
                DataRow dr = dt.NewRow();
                string cmdtext = @"SELECT COUNT(Sub.adj_longid) AS Cnew,SUM(Sub.adj_nowmoney) AS Snew FROM 
                                (SELECT DISTINCT adj_longid,adj_nowmoney FROM subsidyrecord WHERE adj_starttime=" + time + " "
                                + " AND adj_nowmoney=adj_moneychange AND adj_checked=1 AND adj_longid IN "
                                + " (SELECT apply_longid FROM Apply WHERE apply_street='" + arrayadd[i] + "')) AS Sub";
                SqlDataReader sqldr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString,CommandType.Text,cmdtext);
                if (sqldr.Read())
                {
                    Snew = float.Parse(string.IsNullOrEmpty(sqldr["Snew"].ToString()) ? "0" : sqldr["Snew"].ToString());
                    Cnew = float.Parse(sqldr["Cnew"].ToString());
                }
                sqldr.Close();

                cmdtext = @"SELECT COUNT(Sub.adj_longid) AS Cadd,SUM(Sub.adj_nowmoney) AS Sadd FROM "
                            + " (SELECT DISTINCT adj_longid,adj_nowmoney FROM subsidyrecord WHERE adj_starttime=" + time + " "
                            + " AND adj_nowmoney!=adj_moneychange AND adj_moneychange>0 AND adj_checked=1 "
                            + " AND adj_longid IN (SELECT apply_longid FROM Apply WHERE apply_street='" + arrayadd[i] + "')) AS Sub";
                SqlDataReader sqldr1 = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
                if (sqldr1.Read())
                {
                    Sadd = float.Parse(string.IsNullOrEmpty(sqldr1["Sadd"].ToString()) ? "0" : sqldr1["Sadd"].ToString());
                    Cadd = float.Parse(sqldr1["Cadd"].ToString());
                }
                sqldr1.Close();

                cmdtext = @"SELECT COUNT(Sub.adj_longid) AS Cred,SUM(Sub.adj_nowmoney) AS Sred FROM "
                          + " (SELECT DISTINCT adj_longid,adj_nowmoney FROM subsidyrecord WHERE adj_starttime=" + time + " "
                          + " AND adj_nowmoney!=adj_moneychange AND adj_moneychange<0 AND adj_checked=1 "
                          + " AND adj_longid IN (SELECT apply_longid FROM Apply WHERE apply_street='" + arrayadd[i] + "')) AS Sub";
                SqlDataReader sqldr2 = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
                if (sqldr2.Read())
                {
                    Sred = float.Parse(string.IsNullOrEmpty(sqldr2["Sred"].ToString()) ? "0" : sqldr2["Sred"].ToString());
                    Cred = float.Parse(sqldr2["Cred"].ToString());
                }
                sqldr2.Close();

                cmdtext = @"SELECT COUNT(Bf.longid) AS Cbf,SUM(Bf.money) AS Sbf FROM"
                           + " (SELECT DISTINCT longid,money FROM Reissue WHERE checked=1 AND reissuetime=" + time + " "
                           + " AND longid IN (SELECT apply_longid FROM Apply WHERE apply_street='" + arrayadd[i] + "')) AS Bf";
                SqlDataReader sqldr3 = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
                if (sqldr3.Read())
                {
                    Cbf = float.Parse(sqldr3["Cbf"].ToString());
                    Sbf = float.Parse(string.IsNullOrEmpty(sqldr3["Sbf"].ToString()) ? "0" : sqldr3["Sbf"].ToString());
                }
                sqldr3.Close();

                cmdtext = @"SELECT COUNT(Total.id) AS Ctotal,SUM(Total.money) AS Stotal FROM
                            (SELECT DISTINCT Apply.apply_longid AS id,SubsidyRecord.adj_nowmoney AS money FROM 
                            Apply INNER JOIN SubsidyRecord ON Apply.apply_longid=SubsidyRecord.adj_longid 
                            WHERE Apply.apply_passed = 3 AND Apply.apply_updchecked = 1 AND SubsidyRecord.adj_checked=1 
                            AND SubsidyRecord.adj_nowmoney!=0 AND 
                            SubsidyRecord.adj_starttime<=" + time + " AND SubsidyRecord.adj_stoptime>" + time + " AND Apply.apply_street='" + arrayadd[i] + "') AS Total";
                SqlDataReader sqldr4 = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
                if (sqldr4.Read())
                {
                    Ctotal = float.Parse(sqldr4["Ctotal"].ToString());
                    Stotal = float.Parse(string.IsNullOrEmpty(sqldr4["Stotal"].ToString()) ? "0" : sqldr4["Stotal"].ToString());
                }

                dr[0] = arrayadd[i];
                dr[1] = Cnew;
                dr[2] = Cadd;
                dr[3] = Cred;
                dr[4] = Cbf;
                dr[5] = Snew;
                dr[6] = Sadd;
                dr[7] = Sred;
                dr[8] = Sbf;
                dr[9] = Ctotal;
                dr[10] = Stotal;
                dt.Rows.Add(dr);
                sqldr4.Close();
            }
            //使用报表对象加载报表
            ReportDocument myReport = new ReportDocument();
            string reportPath = Server.MapPath("~/CrystalReport/SubsidyReleaseSituation.rpt");
            myReport.Load(reportPath);
            //绑定数据集，注意，一个报表用一个数据集。
            myReport.SetDataSource(dsc);


            CrystalReportViewer1.ReportSource = myReport;
            ParameterFields paramFields = new ParameterFields();
            ParameterField paramField = new ParameterField();
            ParameterDiscreteValue pdValue = new ParameterDiscreteValue();

            paramField.ParameterFieldName = "Time";
            pdValue.Value = ddlyear.SelectedValue + "年" + ddlmonth.SelectedValue + "月份";
            paramField.CurrentValues.Add(pdValue);
            paramFields.Add(paramField);

            CrystalReportViewer1.ParameterFieldInfo = paramFields;
        }
        else
            WebHelper.AlertAndRedirect("登陆超时，请重新登陆","../Login.aspx");
    }
    public void BindDate()
    {
        int year = Convert.ToInt32(DateTime.Now.Year.ToString());
        for (int i = year - 2; i < year + 3; i++)
        {
            ddlyear.Items.Add(i.ToString());
        }
        ddlyear.SelectedValue = DateTime.Now.Year.ToString();
        ddlmonth.SelectedValue = DateTime.Now.Month.ToString();
    }
}
